Mega Code Archive
PL SQL Data Types
1) A Better Routine to Check Social Security Numbers
2) Accessing an entire record
3) Accessing Individual Record elements
4) Add 1 year, 2 months, 3 days, 4 hours, 5 minutes, 6 7 seconds
5) Add date interval to timestamp
6) Add some days, hours, minutes, and seconds
7) Add some years and months
8) ADD_MONTHS
9) Adding and subtracting intervals
10) All identifiers within the same scope must be unique
11) All logical operations (including NOT) involving NULL always return NULL
12) An attempt to assign a negative value to a POSITIVE variable
13) ANYTYPE Demo
14) ASCII
15) Assign that date variable to another string
16) Assign the string to a date variable The conversion is implicit
17) Assign value to NUMBER type variable
18) Assigning a Fraction to an Integer
19) Assigning an empty string to the character variable is exactly the same as assigning NULL to it
20) Assigning Record Variables
21) Assignment value to DATE type variable during declaration
22) Attempt to assign a single white space to a number
23) BFILE type variable
24) BINARY_DOUBLE Demo
25) BINARY_INTEGER
26) BINARY_INTEGER in action
27) BINARY_INTEGER Subtypes
28) Blob locator
29) Block Structure
30) BOOLEAN
31) Boolean literals
32) Boolean type parameter
33) Boolean variables can be assigned either directly by using values TRUE, FALSE, or NULL or as the results of logical expr
34) Building Expressions with Operators
35) Calculate the months between two dates
36) CHAR
37) CHAR type variable
38) Check the last date that a date type variable can hold
39) Clob type column and clob type variable
40) Close the BFILE
41) Column type parameters
42) Column type variable
43) Combine TO_NUMBER and TO_DATE to get different parts of a Date
44) Compare a variable length string against a fixed length, and the trailing spaces do matter
45) Compare char against varchar, and the trailing spaces do matter
46) Compare CHAR and VARVHAR32 variables for equality
47) Compare char type value with blank space
48) Compare char type value with trim
49) Compare DATE type variable after truncating
50) Compare DATE type variable in IF statement
51) Compare fixed length string and a literal
52) Compare Integer value with NULL value
53) Compare NULL value and OR operator
54) Compare with NULL value and AND operator
55) Compare with NULL value for equality
56) Compare with NULL value for non-equality
57) Comparison of a fixed length string and a literal
58) Comparison of CHAR with VARCHAR2
59) Concatenate both string variables and constants
60) Concatenate several string constants
61) Concatenate two string variables
62) Constants are compared using blank-padded comparison semantics
63) Constants are compared using blank-padded comparison semantics, so the trailing spaces wont affect the result
64) Constants cannot be changed
65) Convert Number to VARCHAR implicitly
66) Convert VARCHAR to Number implicitly
67) Converting a Character Formatted as Currency to an Integer Value
68) Converting a Character to an Integer Value
69) Converting DATE to a Spelled-Out Character Format
70) Converting Numbers to Characters Formatted as a Numeric String
71) Converting VARCHAR2 Percentage Data to a Decimal Equivalent
72) Create Record based on table column type
73) Datatype Conversions
74) DATE
75) Date literals
76) DAY TO SECOND examples
77) Deal with blob data
78) Declare ROWTYPE variable
79) Declaring TIMESTAMP Datatype
80) Define DATE type variable
81) Define DATE type variable, assign value TRUNC(SYSDATE)
82) Define Integer variable and assign value
83) Define NATURAL type variable
84) Define POSITIVE type variable
85) Defining and using a cursor-oriented record
86) Defining Your Own Datatypes
87) Demonstrate the use of SYS AnyData
88) Demonstrates that empty strings are NULL
89) Demonstrates the effects of NULLs on Boolean expressions
90) Demonstrations of SQL functions being applied to CLOB values
91) Determine and display years and months
92) Displaying Date and Time
93) Double the interval
94) Exit when a boolean conditioin
95) Explicit Conversion
96) Explicit declaration of format mask
97) Explicit declaration of format mask in where clause
98) Expressing in Scientific Notation
99) Extending your options with regular expressions
100) Extract and display the URL
101) Find the location of 0303 using INSTRC
102) Fixed length strings are also compared with blank-padded comparison semantic
103) Fixed length strings are compared with blank-padded comparison semantic
104) Get value from different parts of a date using TO_NUMBER function
105) Handling numeric data types in built-in functions
106) assign a TRUE value to a variable of type boolean
107) Identical declarations using NUMBER subtypes
108) IF statement with number value check
109) Implicit Declaration
110) In PLSQL the Boolean expression NULL=NULL evaluates to FALSE
111) Insert into returning into clob
112) Insert into returning to blob type variable
113) INSERT statement involving entire records
114) Inserts and updates using record variables
115) INSTR
116) INSTR2(x, UNISTR(D834))
117) INTERVAL includes two datatypes that allow you to set appropriate durations more precisely
118) Interval multiplication
119) Interval value expressions
120) Introducing the Main Data type Groups
121) Keeping Code Consistent with User-Defined Subtypes
122) LAST_DAY
123) Length of service for INTERVAL YEAR TO MONTH
124) Logic operator and boolean value
125) LONG RAW
126) LONG type display length is default to 80
127) Looping through multiple records
128) Looping through Records in a Cursor
129) MONTHS_BETWEEN
130) MSLABEL
131) NATURAL value Computation
132) Nine digits for fractional seconds, 24-hour clock, 14
133) No fractional seconds at all
134) No time zone, defaults to session time zone
135) NUMBER
136) NUMBER and BINARY_INTEGER Data types
137) NUMBER Data type
138) NUMBER Subtypes
139) Number type variable
140) NUMBER(1,-2)
141) Open the target CLOB and the source BFILE
142) PAD, RPAD, LPAD
143) Passing Variables without Copying
144) Perform a field-by-field comparison
145) Performing Calculations on a Converted Date
146) PLS_INTEGER
147) PLS_INTEGER in action
148) PLS-00382
149) PLSQL allows you to use the %type attribute in a nesting variable declaration
150) PLSQL datatypes
151) Print the current date
152) Print the current date and timestamp
153) RAW
154) Record Variables
155) Record Variables Based on Tables
156) Records
157) Records based on tables can also be used in a SELECT statement
158) Reference attribute in a ROWTYPE variable
159) Remember That Dates Are Numbers
160) REPLACE
161) Represents the old way of placing quotes inside the text, namely to double them
162) Retrieve the LOB locator created by the previous INSERT statement
163) Retrieving Cursor Variables with a Record Variable
164) Returning a point just before a specific midnight with TRUNC
165) ROUND
166) ROUND function being applied to datetime values
167) ROWID
168) Rowtype iterations
169) Save data to clob type column
170) Save value to long-type column and raw-type column
171) Select a number value from a table into a variable and output it
172) Select into table%rowtype
173) Select value into a column type variable
174) Select value into Define column type variable
175) Select value into the %ROWTYPE type variable
176) Setting Precision and Scale
177) Short-circuit evaluation with conditional structures
178) Show some interval division
179) Stored procedure to display the contents of the col_blob and col_clob columns
180) Storing elapsed time with INTERVAL DAY TO SECOND
181) SUBSTR
182) SYS XMLTYPE type column
183) Test the strings for equality
184) Testing for equality of records
185) The %TYPE Command Illustrated
186) The maximum lengths of varchar2 strings do not matter, only the assigned values
187) The most useful character specifications are
188) The scope test script
189) The slash character () at the end of the example executes the PLSQL
190) The TIMESTAMP datatype stores the datetime as follows
191) The TIMESTAMP WITH LOCAL TIME ZONE datatype stores the datetime
192) The TIMESTAMP WITH TIME ZONE datatype stores the datetime
193) The underlying database datatype for this example is Unicode UTF-8
194) TIMESTAMP literals
195) TIMESTAMP WITH LOCAL TIME ZONE
196) TIMESTAMP WITH TIME ZONE in action
197) TO_CHAR(C,YYYY-MM-DD HH
198) TO_DATE(29-DEC-1988,dd-mon-yyyy)
199) TRANSLATE
200) TRIM
201) Trim a text string in PLSQL
202) TRUNC and ROUND
203) Two digits for fractional seconds
204) UPDATE statement involving entire records
205) UROWID type
206) Use a SYS HttpUriType value to retrieve a message from a URL
207) Use and to connect two boolean expressions
208) Use BOOLEAN type variable
209) Use FOR LOOP to loop through dates
210) Use INTERVAL types
211) Use IS NULL in IF statement
212) Use NVL in IF statement to deal with the NULL value comparison
213) Use PLS_INTEGER as the loop counter
214) Use rowtype with object table
215) Use select into statement to assign systimestamp to timestamp variable
216) Use SYSDATE to initialize an DATE type variable
217) Use table column type as the record attribute type
218) Use the date value back and forth
219) Use the new EXTRACT function to grab individual year and month components
220) Use the SUBSTR and INSTR at the same time
221) Use TO_CHAR in PLSQL
222) Use TO_DATE to assign value to DATE type variable
223) Use TO_NUMBER to assign value to NATURAL type variable
224) Use TRUNC to get only the time
225) Useful character built-in functions
226) Using %TYPE and %ROWTYPE on Row Objects
227) Using a Weak REF CURSOR and %ROWTYPE
228) Using an anonymous PLSQL script like the following one
229) Using BINARY_FLOAT and BINARY_DOUBLE for complex calculations
230) Using Format Masks mixed with user string
231) Using Objects in PLSQL
232) Using Records with select into
233) Using Single Quote Characters as Part of Text Strings
234) Using the DATE Format Mask
235) Using the Record Datatype and its limitation
236) Using TIMESTAMP
237) Using TIMESTAMP WITH TIME ZONE
238) VARCHAR2
239) VARCHAR2 type variable
240) Varchar2 type with default value
241) Variable
242) Variable Naming Rules
243) Variable scope
244) Variable visibility
245) Variables Based on Database Columns
246) What is a LONG type
247) When comparing CHAR strings against VARCHAR2 strings, use the rtrim function to eliminate trailing spaces
248) Workarounds for defining intervals
249) Working with dates and built-in functions EXTRACT
250) Writes into a CLOB
251) XMLType CreateXML
252) XMLType getStringVal
253) You can declare the whole string to be enclosed in quotes by using the construct q!text!
254) You may also specify a variables type using the %TYPE keyword, which tells PLSQL to use the same type as a specified col
255) Your First PLSQL Block